package ct.zxd.db;
import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import ct.zxd.bean.*;


public class Dao {
	
	public List<Map<String,Object>> getTableByid(String tableName, String shopid, String showid) throws SQLException{
		Connection conn=DBconn.getconn();
		
		List<Map<String,Object>> list=null;
		if(conn!=null){	
			String orderBy = "";
			String nameSelection = "";
			if(showid!=null && showid!=""){
				nameSelection = "and show_id=?";
			}
			if(tableName.equals("dish")){
				if(showid==null){
					orderBy = "order by show_id+0";
				}else{
					orderBy = "order by category_id";
				}
			}
			if(tableName.equals("category")){
				orderBy = "order by show_id+0";
			}
			String sql="select * from "+tableName+" where shop_id=? "+nameSelection+orderBy;
			PreparedStatement pstm=conn.prepareStatement(sql);
			pstm.setString(1, shopid);
			if(!nameSelection.equals("")){
				pstm.setString(2, showid);
			}
			ResultSet rs=pstm.executeQuery();
			list=DBconn.getList(rs);	
			
			DBconn.closeconn(conn);
		}else{
			 throw new SQLException("数据库连接错误");
		}
		return list;
	}
	
	public List<Map<String,Object>> getDishById(String dishid, String flag) throws SQLException{
		Connection conn=null;
		List<Map<String,Object>> list=null;
		conn=DBconn.getconn();
		if(conn!=null){
			String sql = "select * from dish where front = 1";
			if (flag.equals("detail")) {
				sql = "select * from dish where id=?";
			}
			if (flag.equals("search")) {
				sql = "select * from dish where name like ? ";
			}
			
			PreparedStatement pstm=conn.prepareStatement(sql);
			if (flag.equals("detail")) {
				pstm.setString(1, dishid);
			}
			if (flag.equals("search")) {
				pstm.setString(1, "%"+dishid+"%");
			}
//			System.out.println(dishid);
		    ResultSet rs=pstm.executeQuery();	
		    list=DBconn.getList(rs);
		    DBconn.closeconn(conn);
		}else{
			 throw new SQLException("数据库连接错误");
		}
		return list;	
	}
	
	public boolean isExisted(String tableName,String shopid,String name,String showid) throws SQLException{
		Connection conn=null;
		boolean result=false;
		conn=DBconn.getconn();
		if(conn!=null){
			String sql="select * from "+ tableName+" where name=? and shop_id=? and show_id not in (?)";
			PreparedStatement pstm=conn.prepareStatement(sql);
			pstm.setString(1, name);
			pstm.setString(2, shopid);
			pstm.setString(3, showid);
		    ResultSet rs=pstm.executeQuery();	
		    if(rs.next())result=true;
		    DBconn.closeconn(conn);
		}
		return result;	
	}
	
	public String loginCheck(String shopid) throws SQLException{
		Connection conn=null;
		String result=null;
		conn=DBconn.getconn();
		if(conn!=null){
			String sql="select password from shop where name=?";
			PreparedStatement pstm=conn.prepareStatement(sql);
			pstm.setString(1, shopid);
		    ResultSet rs=pstm.executeQuery();	
		    if(rs.next())result=rs.getString(1);
		    DBconn.closeconn(conn);
		}
		return result;	
	}
	
	
	public int insertDish(String name, String dishid, String shop, float price,int stock,String category,
			String imageURL,String categoryid, String front, String introduce) throws SQLException{
			Connection conn=null;
			int result=0;	
			conn=DBconn.getconn();
			if(conn!=null){		
			String sql="insert into dish(show_id,name,imageURL,price,stockNumber,shop_id,category_name,category_id,front,introduce) "
					+"values(?,?,?,?,?,?,?,?,?,?)";
					PreparedStatement pstm=conn.prepareStatement(sql);
					pstm.setString(1, dishid);
					pstm.setString(2, name);
					pstm.setString(3, imageURL);
					pstm.setFloat(4, price);
					pstm.setInt(5, stock);
					pstm.setString(6, shop);
					pstm.setString(7, category);
					pstm.setString(8, categoryid);
					pstm.setString(9, front);
					pstm.setString(10, introduce);
					result=pstm.executeUpdate();//执行更新操作
					DBconn.closeconn(conn);//关闭连接
			}		
			return result;
	}
	
	public int deleteDish(String dishid,String shopid) throws SQLException{
		Connection conn=null;
		int result=0;	
		conn=DBconn.getconn();
		if(conn!=null){		
				String sql="delete from dish where show_id=? and shop_id=?";
				PreparedStatement pstm=conn.prepareStatement(sql);
				pstm.setString(1, dishid);
				pstm.setString(2, shopid);
				result=pstm.executeUpdate();//执行更新操作
				DBconn.closeconn(conn);//关闭连接
		}		
		return result;
	}
	
	public int updateDish(String name, String dishid, String shop, float price,int stock,
			String category,String imageURL,String categoryid, String front, String introduce) throws SQLException{
		Connection conn=null;
		int result=0;	
		conn=DBconn.getconn();
		if(conn!=null){		
				String sql="update dish set name=?,price=?,stockNumber=?,category_name=?,imageURL=?,"
						+ "category_id=?,front=?,introduce=? where show_id=? and shop_id=?";
				PreparedStatement pstm=conn.prepareStatement(sql);
				pstm.setString(1, name);
				pstm.setFloat(2, price);
				pstm.setInt(3, stock);
				pstm.setString(4, category);
				pstm.setString(5, imageURL);
				pstm.setString(6, categoryid);
				pstm.setString(7, front);
				pstm.setString(8, introduce);
				pstm.setString(9, dishid);
				pstm.setString(10, shop);
				
				result=pstm.executeUpdate();//执行更新操作
				DBconn.closeconn(conn);//关闭连接
		}		
		return result;
	}
	
	
	
	public int insertCategory(String name, String cateid, String shop, String cateshow) throws SQLException{
		Connection conn=null;
		int result=0;	
		conn=DBconn.getconn();
		System.out.println("dao "+name);
		
		if(conn!=null){		
		String sql="insert into category "+"values(?,?,?,?)";
				PreparedStatement pstm=conn.prepareStatement(sql);
				pstm.setString(1, name);
				pstm.setString(2, cateid);
				pstm.setString(3, shop);
				pstm.setString(4, cateshow);
				result=pstm.executeUpdate();//执行更新操作
				DBconn.closeconn(conn);//关闭连接
		}		
		return result;
	}

  public int deleteCategory(String cateid) throws SQLException{
	Connection conn=null;
	int result=0;	
	conn=DBconn.getconn();
	if(conn!=null){		
			String sql="delete from category where category_id = ?";
			PreparedStatement pstm=conn.prepareStatement(sql);
			pstm.setString(1, cateid);
			result=pstm.executeUpdate();//执行更新操作
			DBconn.closeconn(conn);//关闭连接
	}		
	return result;
  }

  public int updateCategory(String cateid, String name) throws SQLException{
	Connection conn=null;
	int result=0;	
	conn=DBconn.getconn();
	if(conn!=null){		
			String sql="update category set name = ? where category_id = ?";
			PreparedStatement pstm=conn.prepareStatement(sql);
			pstm.setString(1, name);
			pstm.setString(2, cateid);
			result=pstm.executeUpdate();//执行更新操作
			DBconn.closeconn(conn);//关闭连接
	}		
	return result;
  }
	
	
  public List<Map<String,Object>> getDishList(int displayPage,int pageSize,String condition) throws SQLException{
	        
	  	Connection conn=DBconn.getconn();
	  	
		List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
		if(conn!=null){
			String sql="select * from ordertable "+condition+" order by id desc LIMIT ?,?";
			PreparedStatement pstm=conn.prepareStatement(sql);
			pstm.setInt(1, (displayPage-1)*pageSize);
			pstm.setInt(2, pageSize);
			ResultSet rs=pstm.executeQuery();
			list=DBconn.getList(rs);			
			DBconn.closeconn(conn);
		}else{
			 throw new SQLException("数据库连接错误");
		}
		return list;	
  }
	
  public int getRowNum(String tableName,String condition)throws SQLException{
		Connection conn=DBconn.getconn();
		int result=0;
		if(conn!=null){
			String sql="select count(*) from "+ tableName+" "+condition;
			Statement stm=conn.createStatement();
			ResultSet rs=stm.executeQuery(sql);
			if(rs.next()){
				result=rs.getInt(1);	
			}
			DBconn.closeconn(conn);
		}
		return result;
	}
	
  	
	
  	public String fetchShopPost(String shopid) throws SQLException{
		Connection conn=null;
		String result=null;
		conn=DBconn.getconn();
		if(conn!=null){
			String sql="select introduce from shop where shop_id=?";
			PreparedStatement pstm=conn.prepareStatement(sql);
			pstm.setString(1, shopid);
		    ResultSet rs=pstm.executeQuery();	
		    if(rs.next())result=rs.getString(1);
		    DBconn.closeconn(conn);
		}
		return result;	
	}
  	
  	public int updateShopPost(String ids) throws SQLException{
		Connection conn=null;
		int result=0;	
		conn=DBconn.getconn();
		if(conn!=null){		
				String sql="update shop set introduce=? where shop_id=1";
				PreparedStatement pstm=conn.prepareStatement(sql);
				pstm.setString(1, ids);
				result=pstm.executeUpdate();//执行更新操作
				DBconn.closeconn(conn);//关闭连接
		}		
		return result;
	}
	
  
}
